package com.fangcun.common.util.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.net.URL;
import java.net.URLConnection;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;

import com.fangcun.common.util.date.DateUtils;

/**
 * @作者： 陳曉鬆 @时间： 2019年5月23日 - 下午1:52:44 @description：->
 */
public class ExcelUtils {

	private static Logger LOG = LoggerFactory.getLogger(ExcelUtils.class);

	/**
	 * 获取数据
	 * 
	 * @param urlPath
	 *            网络excel地址
	 * @return 获取map数据用name_下标 0开始
	 * @throws Exception
	 */
	public static List<Map<String, String>> readExcel(String urlPath) {
		List<Map<String, String>> list = null;
		try {
			URL url = new URL(urlPath);
			URLConnection conn = url.openConnection();
			conn.setConnectTimeout(3000);
			conn.setReadTimeout(3 * 60 * 1000);
			InputStream is = conn.getInputStream();
			// 工作表
			Workbook workbook = WorkbookFactory.create(is);
			// 表个数。
			int numberOfSheets = workbook.getNumberOfSheets();
			list = new ArrayList<Map<String, String>>();
			// 遍历表。
			for (int i = 0; i < numberOfSheets; i++) {
				Sheet sheet = workbook.getSheetAt(i);
				// 行数。
				int rowNumbers = sheet.getLastRowNum() + 1;
				// Excel第一行。
				Row temp = sheet.getRow(0);
				if (temp == null) {
					continue;
				}
				Map<String, String> lableName = new HashMap<String, String>();
				int cells = temp.getPhysicalNumberOfCells();
				for (int col = 0; col < cells; col++) {
					Cell value = temp.getCell(col);
					String name = value == null ? "无属性说明" : value.toString();
					lableName.put("name_" + col, name);
				}
				// 读数据。
				for (int row = 0; row < rowNumbers; row++) {
					Row r = sheet.getRow(row);
					Map<String, String> map = new HashMap<String, String>();
					for (int col = 0; col < cells; col++) {
						Cell value = r.getCell(col);
						String val = StringUtils.isEmpty(value) ? "" : value.toString();
						val = (val.length() > 2 && ".0".equals(val.substring(val.length() - 2, val.length()))) ? val.replace(".0", "") : val;
						if (value != null) {
							if (value.getCellType() == CellType.NUMERIC && DateUtil.isCellDateFormatted(value)) {
								val = DateUtils.DF_SEPARATE.get().format(value.getDateCellValue());
							}
						}
						map.put("name_" + col, val);
					}
					list.add(map);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 获取数据
	 * 
	 * @param fliePath
	 *            文件地址地址
	 * @return 获取map数据用name_下标 0开始
	 * @throws Exception
	 */
	public static List<Map<String, String>> readExcelForFlie(String fliePath) {
		List<Map<String, String>> list = null;
		try {
			// 创建输入流，读取Excel
			InputStream is = new FileInputStream(new File(fliePath).getAbsolutePath());
			// 工作表
			Workbook workbook = WorkbookFactory.create(is);
			// 表个数。
			int numberOfSheets = workbook.getNumberOfSheets();
			list = new ArrayList<Map<String, String>>();
			// 遍历表。
			for (int i = 0; i < numberOfSheets; i++) {
				Sheet sheet = workbook.getSheetAt(i);
				// 行数。
				int rowNumbers = sheet.getLastRowNum() + 1;
				// Excel第一行。
				Row temp = sheet.getRow(0);
				if (temp == null) {
					continue;
				}
				Map<String, String> lableName = new HashMap<String, String>();
				int cells = temp.getPhysicalNumberOfCells();
				for (int col = 0; col < cells; col++) {
					Cell value = temp.getCell(col);
					String name = value == null ? "无属性说明" : value.toString();
					lableName.put("name_" + col, name);
				}
				// 读数据。
				for (int row = 0; row < rowNumbers; row++) {
					Row r = sheet.getRow(row);
					Map<String, String> map = new HashMap<String, String>();
					for (int col = 0; col < cells; col++) {
						Cell value = r.getCell(col);
						String val = StringUtils.isEmpty(value) ? "" : value.toString();
						val = (val.length() > 2 && ".0".equals(val.substring(val.length() - 2, val.length()))) ? val.replace(".0", "") : val;
						if (value != null) {
							try {
								if (value.getCellType() == CellType.NUMERIC && DateUtil.isCellDateFormatted(value)) {
									val = DateUtils.DF_SEPARATE.get().format(value.getDateCellValue());
								}
							} catch (Exception e) {
							}
						}
						map.put("name_" + col, val);
					}
					list.add(map);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 
	 * @description：-> 创建excel
	 * 
	 * @throws Exception
	 *
	 * @作者：陳曉鬆
	 * @时间：2019年5月23日 下午2:33:00 @版本：V1.0
	 *
	 */
	public static String writeExcel(List<Map<String, String>> list, String writePath) {
		String path = "";
		try {
			// 在内存中创建一个Excel文件
			@SuppressWarnings("resource")
			SXSSFWorkbook workbook = new SXSSFWorkbook();
			// 给Excel文件创建一张 Sheet1表
			Sheet sheet = workbook.createSheet("Sheet1");
			if (list.size() > 0) {
				Map<Integer, Integer> length = new HashMap<Integer, Integer>();
				Row row = sheet.createRow(0);
				Map<String, String> lableName = list.get(0);
				for (int i = 0; i < lableName.size(); i++) {
					Cell cell = row.createCell(i);
					Object value = lableName.get("name_" + i);
					String val = StringUtils.isEmpty(value) ? "" : value.toString();
					// 在格子中添加相应的值
					cell.setCellValue(val);

					length.put(i, val.getBytes().length);
				}

				for (int j = 1; j < list.size(); j++) {
					row = sheet.createRow(j);
					lableName = list.get(j);
					for (int i = 0; i < lableName.size(); i++) {
						Cell cell = row.createCell(i);
						Object value = lableName.get("name_" + i);
						String val = StringUtils.isEmpty(value) ? "" : value.toString();
						// 在格子中添加相应的值
						cell.setCellValue(val);

						// 得到长度
						if (length.get(i) != null) {
							if (length.get(i) < val.getBytes().length) {
								length.put(i, val.getBytes().length);
							}
						} else {
							length.put(i, val.getBytes().length);
						}
					}
				}

				for (Map.Entry<Integer, Integer> m : length.entrySet()) {
					sheet.setColumnWidth(m.getKey(), m.getValue() * 2 * 128);
				}
			}
			if (!writePath.contains(".xlsx")) {
				path = writePath + "/" + System.currentTimeMillis() + ".xlsx";
			} else {
				path = writePath;
			}
			String filePath = path.substring(0, path.lastIndexOf("/"));
			File f = new File(filePath);
			if (!f.exists()) {
				f.mkdirs();
			}
			LOG.info("-EXCEL-文件地址->{}", path);
			// 文件输出流--》把内存中的excel文件写到磁盘中
			FileOutputStream fos = new FileOutputStream(path);
			workbook.write(fos);
			fos.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return path;
	}
}
